# -*- coding=utf-8 -*-

import time
import urllib.request
import json


def grab_lp_state():
    import re
    lp_dict = {}
    url = "https://als.bmwbrill.cn/Allgemein/Overview.jsp?Pb=lp&Time=" + str(time.time())
    req = urllib.request.urlopen(url)
    content = req.read()
    req.close()
    content = content.decode('utf-8')
    tr_list = re.findall("<tr>(.*?)</tr>", content, flags=re.S)
    product_dict = {'8666898': 'B38A TR1', '8667034': 'B48A TR1', '8667036': 'B48B TR1', '8615214': 'B38A TR0',
                    '8615218': 'B38B TR0', '8616102': 'B48A TR0', '8616126': 'B48B TR0'}

    for tr in tr_list:
        pa_list = re.findall("<td[^>]*>(.*?)</td>", tr, flags=re.S)
        list_len = len(pa_list)
        if list_len == 10:
            machine = pa_list[0]
            state = pa_list[1].replace('&nbsp;', '')
            raw_part = pa_list[7].replace('&nbsp;', '')
            part_number = raw_part.split('-')[0]
            product = product_dict[part_number] if part_number in product_dict else ''
            akz = machine.split(' ')[0]
            mo = {'state': state, 'product': product, 'raw_part': raw_part}
            lp_dict[akz] = mo
    return lp_dict


def get_rtn_dict(db_engine):
    sql = "SELECT rtns,bezeichnung FROM Als_rc.prozessketten WHERE rownum<=50"
    result = db_engine.execute(sql)
    rtn_dict = {}
    for row in result:
        if row['rtns'].find(',') < 0:
            rtn_dict[row['rtns']] = row['bezeichnung']
    return rtn_dict


# 获取当前产量
def query_als_current_output(db_engine, from_time, to_time):
    sql = "SELECT prozessid, rtn, COUNT(*) cnt FROM (SELECT dmcode, rtn, prozessid, azeit, akz, statusid, RANK() OVER (PARTITION BY dmcode,prozessid ORDER BY zeitpunkt DESC) rank FROM Als_rc.teilehist WHERE prozessid IN (200,601) AND rtn IN('8615214', '8615218', '8616102', '8616126') AND zeitpunkt >=TO_DATE('%s','dd.mm.yyyy hh24:mi:ss') AND zeitpunkt < TO_DATE('%s','dd.mm.yyyy hh24:mi:ss') ) GROUP BY prozessid, rtn ORDER BY prozessid, rtn" % (
        from_time, to_time)
    result = db_engine.execute(sql)
    result_list = []
    for row in result:
        result_list.append({
            'rtn': row['rtn'],
            'prozessid': row['prozessid'],
            'cnt': row['cnt']
        })
    return result_list


# 获取排班数据
def get_schicht_list(db_engine):
    sql = "SELECT schichtraster_nr, schicht_nr, schicht_beginn, schicht_ende FROM Als_rc.schicht_zeiten WHERE schichtraster_nr=1"
    result = db_engine.execute(sql)
    result_list = []
    for row in result:
        tmp_dict = {}
        for column, value in row.items():
            tmp_dict[column] = value
        result_list.append(tmp_dict)
    return result_list


# 获取当天生产计划
def get_prod_plan(db_engine, day_of_week):
    sql = "SELECT setting FROM Als_rc.gpvisualisierung WHERE key='EndcontrolCounts_%s' AND rownum<=1" % (day_of_week)
    result = db_engine.execute(sql)
    for row in result:
        setting_str = row['setting']
    day_setting_dict = {}
    if setting_str is not None:
        arr = eval(row['setting'], type('Dummy', (dict,), dict(__getitem__=lambda s, n: n))())
        for item in arr:
            field_name = item['Rtn'] + '-' + str(item['Shift'])
            day_setting_dict[field_name] = item
    return day_setting_dict


def get_grid_data_from_api():
    url = "https://als.bmwbrill.cn/Allgemein/AlsPartCounts.jsp"
    # url = "https://alsi.bmwbrill.cn/Allgemein/AlsPartCounts.jsp"
    req = urllib.request.urlopen(url)
    content = req.read()
    req.close()
    content = content.decode('utf-8')
    arr = eval(content, type('Dummy', (dict,), dict(__getitem__=lambda s, n: n))())
    return arr


def get_passed_second_from_shift_dict(shift_dict, shift_num):
    passed_second = 0
    if shift_num in shift_dict:
        if shift_dict[shift_num]['passed_second'] > shift_dict[shift_num]['total_second']:
            passed_second = shift_dict[shift_num]['total_second']
        else:
            passed_second = shift_dict[shift_num]['passed_second']
    return passed_second


def get_today_passed_second():
    now_timestamp = int(time.time())
    day_time_tuple = time.localtime(now_timestamp)
    day_time_str = time.strftime("%Y-%m-%d 06:00:00", day_time_tuple)
    day_time_tuple = time.strptime(day_time_str, "%Y-%m-%d %H:%M:%S")
    day_timestamp = int(time.mktime(day_time_tuple))
    passed_second = now_timestamp - day_timestamp
    return passed_second


def get_shift_passed_second(shift_info):
    shift_dict = shift_info[0]
    now_timestamp = int(time.time())
    shift1_time_from = shift_dict[1]['time_from']
    day_time_tuple = time.strptime(shift1_time_from, "%d.%m.%Y %H:%M:%S")
    day_timestamp = int(time.mktime(day_time_tuple))
    passed_second = now_timestamp - day_timestamp
    return passed_second


def rebuild_final_inspection_data(arr, shift_dict, schicht_nr):
    from .util import calc_planned_num, calc_forecast_num, to_int
    t = time.localtime()
    day_of_week = int(time.strftime("%w", t))
    arr_len = len(arr)
    current_data = None
    if arr_len > day_of_week:
        day_data = arr[day_of_week]
        if 'Shifts' in day_data:
            shifts = day_data['Shifts']
            current_data = {}
            casting_list = ['B38A', 'B38B', 'B48A', 'B48B']
            type_list = ['Target', 'Current', 'Forecast', 'Planned', 'Deviation']
            # shift 1 passed second
            p1s = get_passed_second_from_shift_dict(shift_dict, 1)
            # shift 1 total second
            t1s = shift_dict[1]['total_second']
            p2s = get_passed_second_from_shift_dict(shift_dict, 2)
            t2s = shift_dict[2]['total_second']
            p3s = get_passed_second_from_shift_dict(shift_dict, 3)
            t3s = shift_dict[3]['total_second']
            # 保存shift 1/2/3的planned值
            areas_week_data = get_current_week_production_info(arr, day_of_week)

            for n, casting_name in enumerate(casting_list):
                # B38A/B38B/B48A/B48B 的生产数据
                area_data = []
                current_week_planned_val = 0
                ps1 = calc_planned_num(shifts[0]['Types'][n]['Target'], p1s, t1s)
                ps2 = calc_planned_num(shifts[1]['Types'][n]['Target'], p2s, t2s)
                ps3 = calc_planned_num(shifts[2]['Types'][n]['Target'], p3s, t3s)
                today_planned = to_int(ps1) + to_int(ps2) + to_int(ps3)
                fs1 = calc_forecast_num(shifts[0]['Types'][n]['Current'], p1s, t1s)
                fs2 = calc_forecast_num(shifts[1]['Types'][n]['Current'], p2s, t2s)
                fs3 = calc_forecast_num(shifts[2]['Types'][n]['Current'], p3s, t3s)

                for i, type_name in enumerate(type_list):
                    row = {'casting_name': type_name}
                    if type_name == 'Target':
                        row['shift_1'] = shifts[0]['Types'][n][type_name]
                        row['shift_2'] = shifts[1]['Types'][n][type_name]
                        row['shift_3'] = shifts[2]['Types'][n][type_name]
                        row['today'] = row['shift_1'] + row['shift_2'] + row['shift_3']
                        row['current_week'] = areas_week_data[n]['Target']
                    elif type_name == 'Current':
                        s1 = shifts[0]['Types'][n][type_name]
                        s2 = shifts[1]['Types'][n][type_name] if schicht_nr > 1 else '-'
                        s3 = shifts[2]['Types'][n][type_name] if schicht_nr > 2 else '-'
                        row['shift_1'] = s1
                        row['shift_2'] = s2
                        row['shift_3'] = s3
                        row['today'] = s1 + to_int(s2) + to_int(s3)
                        row['current_week'] = areas_week_data[n]['Current']
                    elif type_name == 'Forecast':
                        row['shift_1'] = fs1
                        row['shift_2'] = fs2
                        row['shift_3'] = fs3
                        today_forecast = area_data[0]['today']
                        if schicht_nr >= 1:
                            today_forecast += fs1 - area_data[0]['shift_1']
                        if schicht_nr >= 2:
                            today_forecast += fs2 - area_data[0]['shift_2']
                        if schicht_nr >= 3:
                            today_forecast += fs3 - area_data[0]['shift_3']
                        row['today'] = today_forecast
                        row['current_week'] = areas_week_data[n]['Target'] + areas_week_data[n]['Current'] - \
                                              areas_week_data[n]['Planned'] - today_planned
                    elif type_name == 'Planned':
                        row['shift_1'] = ps1
                        row['shift_2'] = ps2
                        row['shift_3'] = ps3
                        row['today'] = today_planned
                        current_week_planned_val = areas_week_data[n]['Planned'] + row['today']
                        row['current_week'] = current_week_planned_val
                    elif type_name == 'Deviation':
                        s1 = shifts[0]['Types'][n]['Current'] - ps1
                        s2 = (shifts[1]['Types'][n]['Current'] - ps2) if schicht_nr > 1 else '-'
                        s3 = (shifts[2]['Types'][n]['Current'] - ps3) if schicht_nr > 2 else '-'
                        row['shift_1'] = s1
                        row['shift_2'] = s2
                        row['shift_3'] = s3
                        row['today'] = to_int(s1) + to_int(s2) + to_int(s3)
                        row['current_week'] = areas_week_data[n]['Current'] - current_week_planned_val
                    area_data.insert(i, row)
                current_data[casting_name] = area_data
    return current_data


def get_shift_info(db_engine):
    from datetime import datetime, timedelta
    from app.helper.util import time_2_second, second_2_time, calc_time_lag, get_timestamp_from_string
    # from app.helper.als import get_schicht_list
    day_second = time_2_second()
    current_timestamp = time.time()
    schicht_list = get_schicht_list(db_engine)
    schift_dict = {}
    schicht_nr = 3
    production_date_string = time.strftime('%d.%m.%Y ')
    pdate = time.strftime('%Y-%m-%d')  # 生产日期

    # print('-------before--------')
    # print(production_date_string)
    # print(pdate)

    for schicht_item in schicht_list:
        time_from_second = int(schicht_item['schicht_beginn'])
        time_to_second = int(schicht_item['schicht_ende'])
        if time_from_second < day_second < time_to_second:
            break
        else:
            if day_second < time_from_second:
                yesterday_stamp = current_timestamp - 3600 * 24
                yesterday_turple = time.localtime(yesterday_stamp)
                production_date_string = time.strftime('%d.%m.%Y ', yesterday_turple)
                pdate = time.strftime('%Y-%m-%d', yesterday_turple)

    for schicht_item in schicht_list:
        shift_num = schicht_item['schicht_nr']
        time_from_second = int(schicht_item['schicht_beginn'])
        time_to_second = int(schicht_item['schicht_ende'])
        if time_from_second > time_to_second:
            # production date is tomorrow date
            from_datetime = datetime.strptime(pdate, "%Y-%m-%d")
            to_datetime = from_datetime + timedelta(days=1)
            to_date = to_datetime.strftime('%d.%m.%Y ')
            time_from = production_date_string + second_2_time(time_from_second)
            time_to = to_date + second_2_time(time_to_second)
            # if day_second > time_from_second:
            #     tomorrow_stamp = current_timestamp + 3600 * 24
            #     tomorrow_turple = time.localtime(tomorrow_stamp)
            #     tomorrow_string = time.strftime('%d.%m.%Y ', tomorrow_turple)
            #     time_from = production_date_string + second_2_time(time_from_second)
            #     time_to = tomorrow_string + second_2_time(time_to_second)
            # else:
            #     yesterday_stamp = current_timestamp - 3600 * 24
            #     yesterday_turple = time.localtime(yesterday_stamp)
            #     yesterday_string = time.strftime('%d.%m.%Y ', yesterday_turple)
            #     time_from = yesterday_string + second_2_time(time_from_second)
            #     time_to = production_date_string + second_2_time(time_to_second)
        else:
            time_from = production_date_string + second_2_time(time_from_second)
            time_to = production_date_string + second_2_time(time_to_second)
        _total_second = calc_time_lag(time_from, time_to, '%d.%m.%Y %H:%M:%S')
        period_end_timestamp = time.time()
        _passed_second = int(round(period_end_timestamp - get_timestamp_from_string(time_from, '%d.%m.%Y %H:%M:%S')))
        _passed_second = _passed_second if _passed_second > 0 else 0
        schift_dict[shift_num] = {
            'time_from': time_from,
            'time_to': time_to,
            'total_second': _total_second,
            'passed_second': _passed_second
        }
        if time_from_second <= day_second < time_to_second:
            schicht_nr = shift_num
    # print('###############')
    # print(production_date_string)
    # print(pdate)
    shift_info = (schift_dict, schicht_nr, pdate)
    return shift_info


def rebuild_rtb2_data(arr):
    from .util import calc_planned_num, calc_forecast_num
    t = time.localtime()
    day_of_week = int(time.strftime("%w", t))
    arr_len = len(arr)
    rtb2_data = None
    if arr_len > day_of_week:
        day_data = arr[day_of_week]
        if 'Shifts' in day_data:
            shifts = day_data['Shifts']
            rtb2_data = {}
            casting_list = ['B38A', 'B38B', 'B48A', 'B48B']
            type_list = ['Target', 'Current', 'Forecast', 'Planned', 'Deviation']
            # shift 1 passed second
            today_passed_second = get_today_passed_second()
            # shift 1 total second
            today_total_second = 86400
            # 保存shift 1/2/3的planned值
            areas_week_data = get_current_week_rtb2_info(arr, day_of_week)

            for n, casting_name in enumerate(casting_list):
                # B38A/B38B/B48A/B48B 的生产数据
                area_data = []
                current_week_planned_val = 0
                today_planned = calc_planned_num(shifts[3]['Types'][n]['Target'], today_passed_second,
                                                 today_total_second)
                today_forecast = calc_forecast_num(shifts[3]['Types'][n]['Current'], today_passed_second,
                                                   today_total_second)
                today_planned_val = today_planned
                if type(today_planned) is not int:
                    today_planned_val = 0

                for i, type_name in enumerate(type_list):
                    row = {'casting_name': type_name}
                    if type_name == 'Target':
                        row['today'] = shifts[3]['Types'][n][type_name]
                        row['current_week'] = areas_week_data[n]['Target']
                    elif type_name == 'Current':
                        row['today'] = shifts[3]['Types'][n][type_name]
                        row['current_week'] = areas_week_data[n]['Current']
                    elif type_name == 'Forecast':
                        row['today'] = today_forecast
                        row['current_week'] = areas_week_data[n]['Target'] + areas_week_data[n]['Current'] - \
                                              areas_week_data[n]['Planned'] - today_planned_val
                    elif type_name == 'Planned':
                        row['today'] = today_planned_val
                        current_week_planned_val = areas_week_data[n]['Planned'] + row['today']
                        row['current_week'] = current_week_planned_val
                    elif type_name == 'Deviation':
                        row['today'] = shifts[3]['Types'][n]['Current'] - today_planned_val
                        row['current_week'] = areas_week_data[n]['Current'] - current_week_planned_val
                    area_data.insert(i, row)
                rtb2_data[casting_name] = area_data
    return rtb2_data


def get_current_week_production_info(arr, day_of_week):
    areas_week_data = [{
        'Target': 0,
        'Current': 0,
        'Forecast': 0,
        'Planned': 0,
        'Deviation': 0
    }, {
        'Target': 0,
        'Current': 0,
        'Forecast': 0,
        'Planned': 0,
        'Deviation': 0
    }, {
        'Target': 0,
        'Current': 0,
        'Forecast': 0,
        'Planned': 0,
        'Deviation': 0
    }, {
        'Target': 0,
        'Current': 0,
        'Forecast': 0,
        'Planned': 0,
        'Deviation': 0
    }]
    for i in range(0, 7):
        day_data = arr[i]['Shifts']
        if not (day_of_week != 0 and i == 0) and len(day_data) > 0:
            areas_week_data[0]['Target'] += day_data[0]['Types'][0]['Target'] + day_data[1]['Types'][0]['Target'] + \
                                            day_data[2]['Types'][0]['Target']
            areas_week_data[1]['Target'] += day_data[0]['Types'][1]['Target'] + day_data[1]['Types'][1]['Target'] + \
                                            day_data[2]['Types'][1]['Target']
            areas_week_data[2]['Target'] += day_data[0]['Types'][2]['Target'] + day_data[1]['Types'][2]['Target'] + \
                                            day_data[2]['Types'][2]['Target']
            areas_week_data[3]['Target'] += day_data[0]['Types'][3]['Target'] + day_data[1]['Types'][3]['Target'] + \
                                            day_data[2]['Types'][3]['Target']
            if i <= day_of_week:
                areas_week_data[0]['Current'] += day_data[0]['Types'][0]['Current'] + \
                                                 day_data[1]['Types'][0]['Current'] + \
                                                 day_data[2]['Types'][0]['Current']
                areas_week_data[1]['Current'] += day_data[0]['Types'][1]['Current'] + \
                                                 day_data[1]['Types'][1]['Current'] + \
                                                 day_data[2]['Types'][1]['Current']
                areas_week_data[2]['Current'] += day_data[0]['Types'][2]['Current'] + \
                                                 day_data[1]['Types'][2]['Current'] + \
                                                 day_data[2]['Types'][2]['Current']
                areas_week_data[3]['Current'] += day_data[0]['Types'][3]['Current'] + \
                                                 day_data[1]['Types'][3]['Current'] + \
                                                 day_data[2]['Types'][3]['Current']
        if i < day_of_week:
            areas_week_data[0]['Planned'] = areas_week_data[0]['Target']
            areas_week_data[1]['Planned'] = areas_week_data[1]['Target']
            areas_week_data[2]['Planned'] = areas_week_data[2]['Target']
            areas_week_data[3]['Planned'] = areas_week_data[3]['Target']
    return areas_week_data


def get_current_week_rtb2_info(arr, day_of_week):
    areas_week_data = [{
        'Target': 0,
        'Current': 0,
        'Forecast': 0,
        'Planned': 0,
        'Deviation': 0
    }, {
        'Target': 0,
        'Current': 0,
        'Forecast': 0,
        'Planned': 0,
        'Deviation': 0
    }, {
        'Target': 0,
        'Current': 0,
        'Forecast': 0,
        'Planned': 0,
        'Deviation': 0
    }, {
        'Target': 0,
        'Current': 0,
        'Forecast': 0,
        'Planned': 0,
        'Deviation': 0
    }]
    for i in range(0, 7):
        day_data = arr[i]['Shifts']
        if not (day_of_week != 0 and i == 0) and len(day_data) > 0:
            areas_week_data[0]['Target'] += day_data[3]['Types'][0]['Target']
            areas_week_data[1]['Target'] += day_data[3]['Types'][1]['Target']
            areas_week_data[2]['Target'] += day_data[3]['Types'][2]['Target']
            areas_week_data[3]['Target'] += day_data[3]['Types'][3]['Target']
            if i <= day_of_week:
                areas_week_data[0]['Current'] += day_data[3]['Types'][0]['Current']
                areas_week_data[1]['Current'] += day_data[3]['Types'][1]['Current']
                areas_week_data[2]['Current'] += day_data[3]['Types'][2]['Current']
                areas_week_data[3]['Current'] += day_data[3]['Types'][3]['Current']
        if i < day_of_week:
            areas_week_data[0]['Planned'] = areas_week_data[0]['Target']
            areas_week_data[1]['Planned'] = areas_week_data[1]['Target']
            areas_week_data[2]['Planned'] = areas_week_data[2]['Target']
            areas_week_data[3]['Planned'] = areas_week_data[3]['Target']
    return areas_week_data


def grab_rm_state():
    import re
    url = "https://als.bmwbrill.cn/Allgemein/Overview.jsp?Pb=RM&Time=" + str(time.time())
    req = urllib.request.urlopen(url)
    content = req.read()
    req.close()
    content = content.decode('utf-8')
    tr_list = re.findall("<tr>(.*?)</tr>", content, flags=re.S)
    rm_dict = {}
    for tr in tr_list:
        pa_list = re.findall("<td[^>]*>(.*?)</td>", tr, flags=re.S)
        list_len = len(pa_list)
        if list_len == 10:
            machine = pa_list[0]
            state = pa_list[1].replace('&nbsp;', '')
            raw_part = pa_list[7].replace('&nbsp;', '')
            part_number = raw_part.split('-')[0]
            akz = machine.split(' ')[0]
            mo = {'state': state, 'part_number': part_number}
            rm_dict[akz] = mo
    return rm_dict


def get_shift_num():
    today_date_str = time.strftime('%Y%m%d', time.localtime())
    today_time_str = time.strftime('%H%M', time.localtime())
    tomorrow_date_str = time.strftime('%Y%m%d',
                                      time.localtime(time.mktime(time.strptime(today_date_str, '%Y%m%d')) + 3600 * 24))
    shift_1_start = int(today_date_str + '0600')
    shift_1_end = int(today_date_str + '1430')
    shift_2_start = int(today_date_str + '1430')
    shift_2_end = int(today_date_str + '2300')
    shift_3_start = int(today_date_str + '2300')
    shift_3_end = int(tomorrow_date_str + '0600')
    tody_date_val = int(today_date_str + today_time_str)
    num = None
    if shift_1_start <= tody_date_val < shift_1_end:
        num = 1
    elif shift_2_start <= tody_date_val < shift_2_end:
        num = 2
    elif shift_3_start <= tody_date_val < shift_3_end:
        num = 3
    return num


def get_shift_data_by_date(date_str, end_data_str=None):
    import sqlite3
    conn = sqlite3.connect('bbf41_nep.db')
    if end_data_str is None:
        select_sql = 'SELECT * FROM bba_deviation WHERE date_str=? LIMIT 1'
        cursor = conn.execute(select_sql, [date_str])
    else:
        select_sql = 'SELECT * FROM bba_deviation WHERE date_str>=? AND date_str<=?'
        cursor = conn.execute(select_sql, [date_str, end_data_str])
    shift_row = cursor.fetchall()
    conn.close()
    return shift_row


def dispose_current_deviation_data(arr):
    shift_data = []
    if type(arr) is list:
        today_date_str = time.strftime('%Y%m%d', time.localtime())
        shift_row = get_shift_data_by_date(today_date_str)
        if len(shift_row):
            shift_data = json.loads(shift_row[0][1])
        shift_len = len(shift_data) - 1
        shift_num = get_shift_num()
        for i, item in enumerate(arr):
            if i > shift_len:
                shift_data.append({"1": 0, "2": 0, "3": 0})
            shift_data[i][str(shift_num)] = item['CastingCurrent'] - item['CastingPlanned']
            if shift_num > 1:
                last_shift_num = shift_data[i][str(shift_num - 1)]
                if shift_data[i][str(shift_num - 1)] % 2 == 1:
                    shift_data[i][str(shift_num - 1)] = last_shift_num + 1
            if 'yesterday' not in shift_data[i]:
                yesterday_date_str = time.strftime('%Y%m%d', time.localtime(
                    time.mktime(time.strptime(today_date_str, '%Y%m%d')) - 3600 * 24))
                yesterday_shift_row = get_shift_data_by_date(yesterday_date_str)
                ysn = 0
                if len(yesterday_shift_row):
                    sd = json.loads(yesterday_shift_row[0][1])
                    ysn = sd[i]['1'] + sd[i]['2'] + sd[i]['3']
                shift_data[i]['yesterday'] = ysn if ysn % 2 == 0 else ysn + 1
            if 'last_week' not in shift_data[i]:
                import datetime
                d = datetime.datetime.now()
                days_count = datetime.timedelta(days=d.isoweekday())
                day_to = d - days_count
                six_days = datetime.timedelta(days=6)
                day_from = day_to - six_days
                date_from = datetime.datetime(day_from.year, day_from.month, day_from.day, 0, 0, 0)
                date_to = datetime.datetime(day_to.year, day_to.month, day_to.day, 23, 59, 59)
                last_week_data_str_start = date_from.strftime('%Y%m%d')
                last_week_data_str_end = date_to.strftime('%Y%m%d')
                last_week_shift_rows = get_shift_data_by_date(last_week_data_str_start, last_week_data_str_end)
                wsn = 0
                if len(last_week_shift_rows):
                    for row in last_week_shift_rows:
                        rj = json.loads(row[1])
                        wsn += rj[i]['1'] + rj[i]['2'] + rj[i]['3']
                shift_data[i]['last_week'] = wsn if wsn % 2 == 0 else wsn + 1
        shift_data_str = json.dumps(shift_data)
        update_deviation(today_date_str, shift_data_str)
    return shift_data


def dispose_current_deviation_data_v1_2(arr):
    shift_data = []
    if type(arr) is list:
        today_date_str = time.strftime('%Y%m%d', time.localtime())
        shift_row = get_shift_data_by_date(today_date_str)
        if len(shift_row):
            shift_data = json.loads(shift_row[0][1])
        shift_len = len(shift_data) - 1
        shift_num = get_shift_num()
        for i, item in enumerate(arr):
            if i > shift_len:
                shift_data.append({"1": 0, "2": 0, "3": 0})
            shift_data[i][str(shift_num)] = item['CastingCurrent'] - item['CastingPlanned']
            if shift_num > 1:
                last_shift_num = shift_data[i][str(shift_num - 1)]
                if shift_data[i][str(shift_num - 1)] % 2 == 1:
                    shift_data[i][str(shift_num - 1)] = last_shift_num + 1
            if 'yesterday' not in shift_data[i]:
                yesterday_date_str = time.strftime('%Y%m%d', time.localtime(
                    time.mktime(time.strptime(today_date_str, '%Y%m%d')) - 3600 * 24))
                yesterday_shift_row = get_shift_data_by_date(yesterday_date_str)
                ysn = 0
                if len(yesterday_shift_row):
                    sd = json.loads(yesterday_shift_row[0][1])
                    ysn = sd[i]['1'] + sd[i]['2'] + sd[i]['3']
                shift_data[i]['yesterday'] = ysn if ysn % 2 == 0 else ysn + 1
            if 'last_week' not in shift_data[i]:
                import datetime
                d = datetime.datetime.now()
                days_count = datetime.timedelta(days=d.isoweekday())
                day_to = d - days_count
                six_days = datetime.timedelta(days=6)
                day_from = day_to - six_days
                date_from = datetime.datetime(day_from.year, day_from.month, day_from.day, 0, 0, 0)
                date_to = datetime.datetime(day_to.year, day_to.month, day_to.day, 23, 59, 59)
                last_week_data_str_start = date_from.strftime('%Y%m%d')
                last_week_data_str_end = date_to.strftime('%Y%m%d')
                last_week_shift_rows = get_shift_data_by_date(last_week_data_str_start, last_week_data_str_end)
                wsn = 0
                if len(last_week_shift_rows):
                    for row in last_week_shift_rows:
                        rj = json.loads(row[1])
                        wsn += rj[i]['1'] + rj[i]['2'] + rj[i]['3']
                shift_data[i]['last_week'] = wsn if wsn % 2 == 0 else wsn + 1
        shift_data_str = json.dumps(shift_data)
        update_deviation(today_date_str, shift_data_str)
    return shift_data


def update_deviation(date_str, shift_data):
    import sqlite3
    conn = sqlite3.connect('bbf41_nep.db')
    find_sql = 'SELECT * FROM bba_deviation WHERE date_str=?;'
    find_cursor = conn.execute(find_sql, [date_str])
    find_list = find_cursor.fetchall()
    if len(find_list):
        update_sql = 'UPDATE bba_deviation SET shift_data=? WHERE date_str=?;'
        conn.execute(update_sql, [shift_data, date_str])
        conn.commit()
    else:
        insert_sql = 'INSERT INTO bba_deviation VALUES (?,?);'
        conn.execute(insert_sql, [date_str, shift_data])
        conn.commit()


def get_tool_number():
    import re
    lp_list = ['LP11', 'LP12', 'LP13', 'LP14', 'LP15', 'LP16',
               'LP21', 'LP22', 'LP23', 'LP24', 'LP25', 'LP26',
               'LP31', 'LP32', 'LP33']
    lp_dict = {}
    # for lp in lp_list:
    #     url = "https://als.bmwbrill.cn/Allgemein/Anlagenabbild.jsp?System=LP&Gruppe=" + lp + "&time=" + str(
    #         time.time())
    #     req = urllib.request.urlopen(url)
    #     content = req.read()
    #     req.close()
    #     content = content.decode('utf-8')
    #     tr_list = re.findall("<tr[^>]*>(.*?)</tr>", content, flags=re.S)
    #     row = tr_list[0]
    #     col_list = re.findall("<th[^>]*>(.*?)</th>", row, flags=re.S)
    #     lp_num = col_list[0]
    #     row = tr_list[3]
    #     col_list = re.findall("<th[^>]*>(.*?)</th>", row, flags=re.S)
    #     try:
    #         tool_num = col_list[0]
    #     except:
    #         tool_num = '--'
    #     lp_dict[lp_num] = tool_num
    from app import db
    from flask import current_app
    db_engine = db.get_engine(current_app, bind='ALS_NEP_Production')
    sql = "SELECT ANLAGEN_KZ,substr(WERKZEUGE,-2,2) as tool_num from anlagenstatus where anlagen_kz like 'LP%'AND ANLAGEN_KZ NOT LIKE'LPE%'"
    rows = db_engine.execute(sql)
    for row in rows:
        akz = row['anlagen_kz']
        tool_number = row['tool_num']
        lp_dict[akz] = tool_number.replace(',', '')
    return lp_dict


def query_heat_treatment_value(db_engine, from_time, to_time=None):
    if to_time is None:
        sql = "SELECT COUNT(*)DMCODE,RTN FROM TEILEHIST WHERE AKZ LIKE'HM0%%'" \
              " AND AKZ!='HM00'" \
              " AND ZEITPUNKT >= TO_DATE('%s','yyyy-mm-dd hh24:mi:ss')" \
              " GROUP BY RTN" % from_time
    else:
        sql = "SELECT COUNT(*)DMCODE,RTN FROM TEILEHIST WHERE AKZ LIKE'HM0%%'" \
              " AND AKZ!='HM00'" \
              " AND ZEITPUNKT >= TO_DATE('%s','yyyy-mm-dd hh24:mi:ss')" \
              " AND ZEITPUNKT < TO_DATE('%s','yyyy-mm-dd hh24:mi:ss')" \
              " GROUP BY RTN" % (from_time, to_time)
    return db_engine.execute(sql)


def total_volumes(rows):
    value = 0
    for row in rows:
        value += row[0]
    return value


def get_heat_treatment_volume(db_engine):
    from app.helper.util import get_date_str, get_week_first_date_str, get_hour_range
    today_start_time = get_date_str(0, '%Y-%m-%d')
    from_time = today_start_time + ' 06:00:00'
    result = query_heat_treatment_value(db_engine, from_time)
    result_dict = {}
    for row in result:
        result_dict[row[1]] = row[0]
    week_1_date = get_week_first_date_str(today_start_time)
    from_time = week_1_date + ' 06:00:00'
    week_volumes = query_heat_treatment_value(db_engine, from_time)
    week_quantity = total_volumes(week_volumes)
    last_hour_range = get_hour_range(today_start_time, -1)
    last_hour_volumes = query_heat_treatment_value(db_engine, last_hour_range['from_time'], last_hour_range['to_time'])
    last_hour_quantity = total_volumes(last_hour_volumes)
    current_hour_range = get_hour_range(today_start_time)
    current_hour_volumes = query_heat_treatment_value(db_engine, current_hour_range['from_time'],
                                                      current_hour_range['to_time'])
    current_hour_quantity = total_volumes(current_hour_volumes)
    result_dict['week_quantity'] = week_quantity
    result_dict['last_hour_quantity'] = last_hour_quantity
    result_dict['current_hour_quantity'] = current_hour_quantity
    return result_dict


def get_last_number_dict(conn):
    from .mysql import get_value_by_key
    key = 'nep_als_ffi'
    tmp_dict = {}
    result = get_value_by_key(conn, key)
    if result is not None and 'val' in result:
        json_str = result['val']
        tmp_dict['val'] = json.loads(json_str)
        tmp_dict['update_time'] = result['update_time']
        return tmp_dict


def read_counter_from_api(api_type=None):
    if api_type is None:
        url = "http://lixtix0osmapi01/static/data/FoundryFinalInspectionToolNumberCounter.json?time=" + str(time.time())
    else:
        url = "http://lixtix0osmapi01/static/data/FoundryCoreshopToolNumberCounter.json?time=" + str(time.time())
    req = urllib.request.urlopen(url)
    content = req.read()
    req.close()
    content = content.decode('utf-8')
    r = {}
    try:
        r = json.loads(content)
    except json.decoder.JSONDecodeError:
        pass
    return r


def update_final_inspection_counter(tool_num_dict, akz_dict):
    from .mysql import link_mysql, query_all, exec_sql, join_dict
    from .util import timestamp_2_str

    need_update_flag = True  # 是否需要保存记录的标记

    conn = link_mysql()
    now_timestamp = int(time.time())
    his_akz_data = get_last_number_dict(conn)  # 获取上一次的生产数据

    key = 'nep_als_ffi'
    akz_dict_json = json.dumps(akz_dict)
    if his_akz_data is None:
        sql = "INSERT INTO `key_value_pair` (idx, val, update_time) values ('%s', '%s', %s)" % \
              (key, akz_dict_json, now_timestamp)
    else:
        his_akz_dict = his_akz_data['val']

        last_up_time = his_akz_data['update_time'] or 0
        # 设置超过15秒更新数据
        if now_timestamp - last_up_time < 15:
            need_update_flag = False
        sql = "UPDATE `key_value_pair` SET val='%s',update_time='%s' WHERE idx='%s'" % \
              (akz_dict_json, now_timestamp, key)

    if need_update_flag is True:
        conn = link_mysql()
        exec_sql(conn, sql, True)

    # 从表里取出上一次存取的记录
    sql = 'SELECT * FROM `nep_als_ffi_cnt` limit 50;'
    conn = link_mysql()
    result = query_all(conn, sql)
    today_date_value = int(time.strftime('%Y%m%d'))

    idx_by_tool_num = {}  # 将数据库里的数据映射成字典
    tool_counter = {}
    if result is not None:
        for row in result:
            tool_num = row['tool_number']
            # akz = row['last_place_code']
            idx_by_tool_num[tool_num] = row

    # 遍历从数据库里获取的当前的生产数据
    for (tn, item) in tool_num_dict.items():
        save_dict = {'update_time': now_timestamp}
        update_idx = 0
        # 如果在数据库里有相关记录，则进行比对，计算增加量，
        # 如果没有记录，则以当前信息为基础，更新记录
        if tn in idx_by_tool_num:
            update_idx = tn
            db_storage = idx_by_tool_num[tn]
            update_time = db_storage['update_time']
            update_date_value = int(timestamp_2_str(update_time))
            base_number = db_storage['base_number']
            akz = item['akz']
            # 如果tool_num换位置了，则需要更新last_place_code
            if akz != db_storage['last_place_code']:
                save_dict['last_place_code'] = akz
                # 判断是否可以获取到当前tool_num所在工位最近的生产数据。
                # 如果可以获取到，则使用此值做为base_number，否则以当前tool_num所在
                # 工位的生产数据为base_number，即可能会损失一些数据
                if his_akz_dict is not None and akz in his_akz_dict:
                    base_number = his_akz_dict[akz]['value']
                else:
                    base_number = item['value']
                save_dict['base_number'] = base_number
            increment = item['value'] - base_number
            count_value = db_storage['history_counter'] + increment

            # 保证历史记录中的数据不小于0，出现这种情况的原因是生产数据重置为0，而
            # 历史计数没有更新成功导致。
            if count_value < 0:
                count_value = db_storage['history_counter']
                save_dict['base_number'] = item['value']

            tool_counter[tn] = count_value
            # 如果时间为第二天，则需要将前一天的数据累加到history_counter里
            if today_date_value > update_date_value:
                save_dict['history_counter'] = count_value
                save_dict['base_number'] = 0
        else:
            save_dict['tool_number'] = tn
            save_dict['base_number'] = item['value']
            save_dict['last_place_code'] = item['akz']
            save_dict['history_counter'] = 0
            save_dict['reset_time'] = 0
            tool_counter[tn] = 0

        sql_save_frag = join_dict(save_dict)
        if update_idx:
            sql = 'UPDATE `nep_als_ffi_cnt` SET ' + sql_save_frag + ' WHERE tool_number="' + update_idx + '"'
        else:
            sql = 'INSERT INTO `nep_als_ffi_cnt` SET ' + sql_save_frag
        if need_update_flag is True:
            conn = link_mysql()
            exec_sql(conn, sql, True)
    return tool_counter


def query_target_value_by_kind(db_engine, kind_name, from_time, to_time):
    sql = "SELECT SHIFTDAY,SHIFT,KIND,ENGINETYPE,WEEKDAY,TARGETVALUE,ACTUALVALUE,STATUSID " \
          "FROM WEEKLYPARTCOUNTS " \
          "WHERE "
    if kind_name == 'EndcontrolCount':
        sql += "SHIFT = 0 AND "
    elif kind_name == 'CastCount':
        sql += "SHIFT > 0 AND "
    sql += "KIND = '" + kind_name + "' AND SHIFTDAY >= to_date('%s', 'yyyy-mm-dd hh24:mi:ss') " \
                                    "AND SHIFTDAY < to_date('%s', 'yyyy-mm-dd hh24:mi:ss')" % (from_time, to_time)
    return db_engine.execute(sql)


# 从数据库获取铸造的生产数据，engine_kind可以获取数据类型是RTB2还是Casting的。
# to_time - from_time 差值为一天时，有班次信息，超过一天，则不分班次的累加计算
def build_production_volume_by_range(db_eng, from_time, to_time, engine_kind, shift_info, is_current_day):
    from .util import calc_planned_num, calc_forecast_num, compute_the_time_difference, reformat_date_str, \
        string_to_timestamp, get_timestamp_from_string
    _dict = {}
    shift_dict = shift_info[0]
    current_shift_num = shift_info[1]
    production_date_str = shift_info[2]
    production_date_timestamp = string_to_timestamp(production_date_str, '%Y-%m-%d')
    now_timestamp = int(time.time())
    # is_current_day = False  # 是否为当天
    is_more_than_one_day = False  # 是否多于一天
    # print('-------------------------------------------')
    # print('from_time' + from_time)
    # print('to_time' + to_time)
    timestamp_different = compute_the_time_difference(from_time, to_time)
    if timestamp_different > 86400:
        is_more_than_one_day = True
    if engine_kind == 'RTB2':
        kind_name = 'EndcontrolCount'
        data = query_target_value_by_kind(db_eng, kind_name, from_time, to_time)
    else:
        kind_name = 'CastCount'
        data = query_target_value_by_kind(db_eng, kind_name, from_time, to_time)

    for item in data:
        shift_num = item['shift']
        engine_type = item['enginetype']
        from_timestamp = None
        # 如果从数据库中获取的是多天的数据，此时是计算多天的总生产值
        # 不需要用班次做为id的一部分，同时如果指定的日期不是当前日期，则已过时间和总计时间为固定值86400
        if is_more_than_one_day:
            idx = engine_type
            # week_history_target 指的是当周已过天数的target总和，不包括当天
            week_history_target_idx = idx + 'WeekHistoryTarget'
            week_passed_second_idx = idx + 'WeekPassedSecond'
            shift_day = item['shiftday'].strftime('%Y-%m-%d %H:%M:%S')
            item_date = reformat_date_str(shift_day)
            # 如果数据为当天的数据，则不继续进行累加计算，因为需要通过班次信息信息计算产量
            # 当天的数据会单独进行累加
            item_date_timestamp = string_to_timestamp(item_date, '%Y-%m-%d')
            # 遍历一周的数据，如果当前系统日期大于数据的日期，一周用于生产的时间会根据班次类型累加
            # kind 为 CastCount 一天分为三班，如果当班时间有生产数据，则累加8小时时间。如果kind
            # 为 EndcontrolCount，一天为一班，累加24小时的生产时间。
            # 如果数据的日期为当天，kind 为 EndcontrolCount的累加时间，为当前时间减去第一班次的开始时间
            # kind 为CastCount
            if production_date_timestamp > item_date_timestamp:
                if week_history_target_idx in _dict:
                    _dict[week_history_target_idx] += item['targetvalue']
                else:
                    _dict[week_history_target_idx] = item['targetvalue']
                if week_passed_second_idx not in _dict:
                    _dict[week_passed_second_idx] = 0
                if 'actualvalue' in item and item['actualvalue'] and item['actualvalue'] > 0:
                    if shift_num > 0:
                        _dict[week_passed_second_idx] += 28800
                    elif shift_num == 0:
                        _dict[week_passed_second_idx] += 86400
            else:
                if week_history_target_idx not in _dict:
                    _dict[week_history_target_idx] = 0
                if week_passed_second_idx not in _dict:
                    _dict[week_passed_second_idx] = 0
                if production_date_str == item_date:
                    if 'actualvalue' in item and item['actualvalue'] and item['actualvalue'] > 0:
                        if shift_num > 0:
                            if shift_num in shift_dict and 'time_from' in shift_dict[shift_num] and 'time_to' in \
                                    shift_dict[shift_num]:
                                time_from = shift_dict[shift_num]['time_from']
                                time_to = shift_dict[shift_num]['time_to']
                                time_from_timestamp = get_timestamp_from_string(time_from, '%d.%m.%Y  %H:%M:%S')
                                time_to_timestamp = get_timestamp_from_string(time_to, '%d.%m.%Y  %H:%M:%S')
                                if time_from_timestamp < now_timestamp < time_to_timestamp:
                                    _dict[week_passed_second_idx] += now_timestamp - time_from_timestamp
                                elif now_timestamp > time_to_timestamp:
                                    _dict[week_passed_second_idx] += 28800
                        elif shift_num == 0:
                            if 1 in shift_dict and 'time_from' in shift_dict[1]:
                                shift_1_time_from = shift_dict[1]['time_from']
                                shift_1_time_from_timestamp = get_timestamp_from_string(shift_1_time_from, '%d.%m.%Y  %H:%M:%S')
                                _dict[week_passed_second_idx] += now_timestamp - shift_1_time_from_timestamp

            total_second = 86400
            passed_second = 86400
        else:
            from_timestamp = string_to_timestamp(from_time)
            if engine_kind == 'RTB2':
                total_second = 86400
                idx = engine_type
                if is_current_day:
                    passed_second = get_shift_passed_second(shift_info)
                else:
                    passed_second = total_second
            else:
                idx = engine_type + str(shift_num)
                shift_data = shift_dict[shift_num]
                total_second = shift_data['total_second']
                if is_current_day:
                    passed_second = get_passed_second_from_shift_dict(shift_dict, shift_num)
                else:
                    passed_second = total_second
        idx_target = idx + 'Target'
        idx_current = idx + 'Current'
        idx_forecast = idx + 'Forecast'
        idx_planned = idx + 'Planned'
        idx_deviation = idx + 'Deviation'
        if item['targetvalue']:
            if idx_target in _dict:
                _dict[idx_target] = _dict[idx_target] + item['targetvalue']
            else:
                _dict[idx_target] = item['targetvalue']
        elif idx_target not in _dict:
            _dict[idx_target] = 0

        if item['actualvalue']:
            _dict[idx_current] = item['actualvalue'] if idx_current not in _dict \
                else _dict[idx_current] + item['actualvalue']
        elif idx_current not in _dict:
            _dict[idx_current] = 0

        # print('@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@')
        # print('#from_timestamp')
        # print(from_timestamp)
        # print('#production_date_timestamp')
        # print(production_date_timestamp)

        if from_timestamp and from_timestamp > production_date_timestamp:
            forecast_val = 0
            planned_val = 0
        else:
            if idx_target in _dict and _dict[idx_target]:
                if is_current_day:
                    if shift_num <= current_shift_num:
                        planned_val = calc_planned_num(_dict[idx_target], passed_second, total_second)
                    else:
                        planned_val = 0
                else:
                    planned_val = _dict[idx_target]
            else:
                planned_val = 0

            if idx_current in _dict and _dict[idx_current]:
                if is_current_day:
                    forecast_val = calc_forecast_num(_dict[idx_current], passed_second, total_second)
                else:
                    forecast_val = _dict[idx_current]
            else:
                forecast_val = 0

        _dict[idx_forecast] = forecast_val
        _dict[idx_planned] = planned_val
        # print(idx_planned)
        # print(type(_dict[idx_current]))
        # print(type(_dict[idx_planned]))
        if type(_dict[idx_planned]) == str:
            _dict[idx_planned] = 0
        deviation_num = _dict[idx_current] - _dict[idx_planned]
        _dict[idx_deviation] = deviation_num

    # 都从数据库里读了，不需要修正了
    # if is_current_week:
    #     fix_dict = fix_production_data_from_api(engine_kind, current_shift_num, is_more_than_one_day)
    #     _dict = dict(_dict, **fix_dict)
    return _dict


# 将数据库获取的生产数据，转换成响应的结构
def build_foundry_data_for_response(week_data, data_dict, is_current_day, is_current_week, engine_kind, today_data):
    from .util import to_int
    casting_list = ['B38A', 'B38B', 'B48A', 'B48B']
    type_list = ['Target', 'Current', 'Forecast', 'Planned', 'Deviation']
    obj = {}
    week_production = 0
    final_week_passed_second = 0
    week_LR = 0
    for ci in casting_list:
        arr = []
        week_passed_second_idx = ci + 'WeekPassedSecond'
        week_passed_second = 0
        if week_passed_second_idx in week_data:
            week_passed_second = week_data[week_passed_second_idx]
        for ti in type_list:
            current_week = 0
            if engine_kind == 'RTB2':
                idx = ci + ti
                today = 0 if idx not in data_dict else data_dict[idx]
                if idx in week_data:
                    current_week = week_data[idx]
                if ti == 'Current':
                    week_production += current_week
                item_obj = {
                    'casting_name': ti,
                    'today': today,
                    'current_week': current_week
                }
                if week_passed_second > final_week_passed_second:
                    final_week_passed_second = week_passed_second
            else:
                idx1 = ci + "1" + ti
                idx2 = ci + "2" + ti
                idx3 = ci + "3" + ti
                idx_week = ci + ti
                s1 = 0 if idx1 not in data_dict else data_dict[idx1]
                s2 = 0 if idx2 not in data_dict else data_dict[idx2]
                s3 = 0 if idx3 not in data_dict else data_dict[idx3]
                today = to_int(s1) + to_int(s2) + to_int(s3)
                if idx_week in week_data:
                    current_week = week_data[idx_week]
                if ti == 'Current':
                    week_production += current_week
                item_obj = {
                    'casting_name': ti,
                    'shift_1': s1,
                    'shift_2': s2,
                    'shift_3': s3,
                    'today': today,
                    'current_week': current_week
                }
                if week_passed_second > final_week_passed_second:
                    final_week_passed_second = week_passed_second
            arr.append(item_obj)
        if is_current_week:
            week_history_target_idx = ci + 'WeekHistoryTarget'
            if week_history_target_idx in week_data:
                arr[3]['current_week'] = week_data[week_history_target_idx]
                if is_current_day:
                    arr[3]['current_week'] += arr[3]['today']
                else:
                    if today_data:
                        if engine_kind == 'RTB2':
                            idx = ci + 'Planned'
                            today_planned_value = 0 if idx not in data_dict else today_data[idx]
                        else:
                            idx1 = ci + "1" + 'Planned'
                            idx2 = ci + "2" + 'Planned'
                            idx3 = ci + "3" + 'Planned'
                            s1 = 0 if idx1 not in data_dict else today_data[idx1]
                            s2 = 0 if idx2 not in data_dict else today_data[idx2]
                            s3 = 0 if idx3 not in data_dict else today_data[idx3]
                            today_planned_value = to_int(s1) + to_int(s2) + to_int(s3)
                        arr[3]['current_week'] += today_planned_value
            arr[2]['current_week'] = arr[0]['current_week'] + arr[1]['current_week'] - arr[3]['current_week']
            arr[4]['current_week'] = arr[2]['current_week'] - arr[0]['current_week']
        if is_current_day and engine_kind != 'RTB2':
            arr[2]['today'] = arr[0]['today'] + arr[4]['today']
        obj[ci] = arr
    if final_week_passed_second > 0:
        week_LR = round(week_production / (final_week_passed_second / 3600), 1)
    # print(week_production)
    # print(final_week_passed_second)
    # print(final_week_passed_second / 3600)
    obj['week_LR'] = week_LR
    return obj


# 获取数据库里缺失数据所处于一周的周几，
# 比如说今天周五，数据库里只能查到周一至周四的完整数据，而周五的数据只有当前班次之前的数据
# 周五当天后缺少的Target值会单独获取，需要将周六，周日两天的周几数返回，即返回[0, 6]
# 周日用0表示
def get_lost_date_list():
    week_day_number = int(time.strftime('%w'))
    day_list = []
    if week_day_number > 0:
        day_list.append(0)
    for i in range(0, 7):
        if i > week_day_number:
            day_list.append(i)
    return day_list


def get_day_fix_data(data, engine_kind, current_shift_num=None):
    _dict = {}
    if data is not None and 'Shifts' in data:
        shift_list = data['Shifts']
        if engine_kind == 'RTB2':
            o = shift_list[3]
            if 'Types' in o and type(o['Types']) == list:
                for obj in o['Types']:
                    idx = obj['Type'] + 'Target'
                    _dict[idx] = obj['Target']
        else:
            if type(current_shift_num) == int and current_shift_num > 0:
                n = current_shift_num
                for i in range(n, 4):
                    o = shift_list[i - 1]
                    if 'Types' in o and type(o['Types']) == list:
                        for obj in o['Types']:
                            idx = obj['Type'] + str(i) + 'Target'
                            _dict[idx] = obj['Target']
    return _dict


# fix_type 为 False 时，获取天的数据，为 True 时，获取周的数据
def fix_production_data_from_api(engine_kind, current_shift_num, fix_type):
    grid_data = get_grid_data_from_api()
    _dict = {}
    if type(grid_data) == list:
        if fix_type is False:
            week_day_number = int(time.strftime('%w'))
            l = len(grid_data)
            if week_day_number < l:
                da = grid_data[week_day_number]
                _dict = get_day_fix_data(da, engine_kind, current_shift_num)
        else:
            day_list = get_lost_date_list()
            l = len(grid_data)
            for i in day_list:
                if i < l:
                    da = grid_data[i]
                    tmp_dict = get_day_fix_data(da, engine_kind, current_shift_num)
                    dict_keys = tmp_dict.keys()
                    dict_len = len(dict_keys)
                    if dict_len > 0:
                        for k in dict_keys:
                            if k in _dict:
                                _dict[k] += tmp_dict[k]
                            else:
                                _dict[k] = tmp_dict[k]
    return _dict


def get_production_value(db_eng, engine_kind, shift_info):
    from datetime import datetime, timedelta
    production_date = shift_info[2]
    from_time = production_date + " 00:00:00"
    from_datetime = datetime.strptime(production_date, "%Y-%m-%d")
    to_datetime = from_datetime + timedelta(days=1)
    to_date = to_datetime.strftime('%Y-%m-%d')
    to_time = to_date + " 00:00:00"
    return build_production_volume_by_range(db_eng, from_time, to_time, engine_kind, shift_info, True)


def query_data_by_engine_kind(db_eng, from_time, to_time, engine_kind, shift_info):
    from .util import is_date_in_range, get_week_range
    date_range = get_week_range(from_time)
    date1 = date_range['start']
    date2 = date_range['end']
    production_date_str = shift_info[2]

    production_time_str = production_date_str + " 00:00:00"
    is_current_week = is_date_in_range(production_time_str, date1, date2)

    is_current_day = False  # 是否为当天
    if is_current_week:
        is_current_day = True
    # 获取指定日期一周的生产总和
    week_data = build_production_volume_by_range(db_eng, date1, date2, engine_kind, shift_info, is_current_day)
    # print('#####week_data#########')
    # print(date1)
    # print(date2)
    # print(week_data)
    is_current_day = False  # 是否为当天
    if production_time_str == from_time:
        is_current_day = True
    # 获取指定日期的产量
    the_day_data = build_production_volume_by_range(db_eng, from_time, to_time, engine_kind, shift_info, is_current_day)
    today_data = None
    if is_current_week and not is_current_day:
        # 获取当日的产量
        today_data = get_production_value(db_eng, engine_kind, shift_info)
    return build_foundry_data_for_response(week_data, the_day_data, is_current_day, is_current_week, engine_kind, today_data)


def grab_coreshop_state():
    import re
    url = "https://als.bmwbrill.cn/Allgemein/Andoonpanel.jsp" \
          "?Pb=CS&Subgroup=none&Time=1521792402582&Time=" + str(time.time())
    req = urllib.request.urlopen(url)
    content = req.read()
    req.close()
    content = content.decode('utf-8')
    tr_list = re.findall("<tr>(.*?)</tr>", content, flags=re.S)
    rm_dict = {}
    for tr in tr_list:
        pa_list = re.findall("<td[^>]*>(.*?)</td>", tr, flags=re.S)
        list_len = len(pa_list)
        if list_len == 6:
            machine = pa_list[0]
            state = pa_list[1].replace('&nbsp;', '')
            product = pa_list[2].replace('&nbsp;', '')
            akz = machine[0:4]
            if re.match(r'CS\d{0,2}', machine):
                mo = {'state': state.lower(), 'product': product}
                rm_dict[akz] = mo
    return rm_dict


def get_coreshop_info_by_motor_type(db_engine, shift_dict, shift_num):
    from app.helper.util import get_week_range, calc_planned_num, calc_forecast_num
    sql = "SELECT DISTINCT(rohteil_nr),motortyp FROM rohteil WHERE motortyp IN " \
          "('B38A','B38B','B48A','B48B') AND rohteil_nr LIKE '0%' AND bezeichnung NOT LIKE '%CORE PACKAGE'"
    rows = db_engine.execute(sql)
    motor_type_dict = {}
    int_shift_num = int(shift_num)
    current_timestamp = time.mktime(time.localtime())
    today_date_str = time.strftime('%Y-%m-%d')
    week_num = time.strftime('%w')
    int_week_num = int(week_num)
    db_week_day = int_week_num + 1

    day_time_from = shift_dict[1]['time_from']
    day_time_to = shift_dict[3]['time_to']
    time_tuple = time.strptime(day_time_from, '%d.%m.%Y %H:%M:%S')
    shift_1_start_timestamp = time.mktime(time_tuple)
    shift_1_time_str = time.strftime('%H:%M:%S', time_tuple)
    shift_1_date_str = time.strftime('%d.%m.%Y', time_tuple)

    for row in rows:
        blank_num = row['rohteil_nr']  # 形如0465276
        motor_type = row['motortyp']

        if motor_type in motor_type_dict:
            ml = motor_type_dict[motor_type]
        else:
            ml = {}
        # d_s_p_i表示代号为num的设备一天之内各班次的生产数据
        d_s_p_i = get_coreshop_day_shift_pv_info(db_engine, blank_num, shift_dict, shift_num)

        if d_s_p_i:
            for (n, val) in d_s_p_i.items():
                shift_type_idx = 'shift_' + str(n)
                # shift_data 是记录某一班次的数据
                if shift_type_idx in ml:
                    shift_data = ml[shift_type_idx]
                else:
                    shift_data = {}
                if blank_num in shift_data:
                    same_type_val = shift_data[blank_num]
                    val = {'ok': same_type_val['ok'] + val['ok'],
                           'nok': same_type_val['nok'] + val['nok']}
                shift_data[blank_num] = val
                ml[shift_type_idx] = shift_data

        day_pv_list = get_coreshop_time_range_pv_info(db_engine, blank_num, day_time_from, day_time_to)

        if 'today' in ml:
            day_data = ml['today']
        else:
            day_data = {}
        day_data[blank_num] = day_pv_list
        ml['today'] = day_data
        week_range = get_week_range(shift_1_date_str, '%d.%m.%Y')
        week_time_from = week_range['start'] + ' ' + shift_1_time_str
        week_time_to = week_range['end'] + ' ' + shift_1_time_str
        week_pv_list = get_coreshop_time_range_pv_info(db_engine, blank_num, week_time_from, week_time_to)

        if 'current_week' in ml:
            week_data = ml['current_week']
        else:
            week_data = {}
        week_data[blank_num] = week_pv_list
        ml['current_week'] = week_data
        motor_type_dict[motor_type] = ml
    # print('#d')
    # print(motor_type_dict)
    week_date_range = get_week_range(today_date_str, '%Y-%m-%d')

    week_start_time = week_date_range['start'] + ' 00:00:00'
    week_end_time = week_date_range['end'] + ' 00:00:00'

    # 获取一周的目标产量
    target_set = query_target_value_by_kind(db_engine, 'CoreshootCount', week_start_time, week_end_time)

    week_target_dict = {}
    current_week_target_dict = {}
    before_day_target_dict = {}
    for target_value in target_set:
        week_day = target_value['weekday']
        if week_day not in week_target_dict:
            week_target_dict[week_day] = {}
        engine_type = target_value['enginetype']
        shift_num = str(target_value['shift'])
        idx = engine_type + '_shift_' + shift_num
        today_idx = engine_type + '_today'
        week_idx = engine_type + '_current_week'
        week_target_dict[week_day][idx] = target_value['targetvalue']
        if today_idx in week_target_dict[week_day]:
            week_target_dict[week_day][today_idx] += target_value['targetvalue']
        else:
            week_target_dict[week_day][today_idx] = target_value['targetvalue']
        if week_idx in current_week_target_dict:
            current_week_target_dict[week_idx] += target_value['targetvalue']
        else:
            current_week_target_dict[week_idx] = target_value['targetvalue']

        if week_day < int_week_num:
            if engine_type not in before_day_target_dict:
                before_day_target_dict[engine_type] = target_value['targetvalue']
            else:
                before_day_target_dict[engine_type] += target_value['targetvalue']

    today_target_dict = None
    if db_week_day in week_target_dict:
        today_target_dict = week_target_dict[db_week_day]

    current_pv_data = {}

    for (mt, mt_val) in motor_type_dict.items():
        tmp_dict = {}
        for (sn, sn_val) in mt_val.items():
            tmp_dict[sn] = get_coreshop_current_pv(sn_val)
        current_pv_data[mt] = tmp_dict
    mtt = ('B38A', 'B38B', 'B48A', 'B48B')
    vtt = ('Target', 'Current', 'Forecast', 'Planned', 'Deviation')
    ft = ('shift_1', 'shift_2', 'shift_3', 'today', 'current_week')
    val_con = {}
    # 'Forecast', 'Planned', 'Deviation'
    # f_p_d_tuple = ('Forecast', 'Planned', 'Deviation')
    f_p_d_dict = {}
    today_passed_sec = int(current_timestamp - shift_1_start_timestamp)

    for mt in mtt:
        tmp_list = []
        for vt in vtt:
            tmp_dict = {'casting_name': vt}
            for field in ft:
                tmp_dict[field] = '-'
                idx = '%s_%s' % (mt, field)
                plan_idx = mt + '_Planned_' + field

                if field == 'today':
                    total_sec = 86400
                    pass_sec = today_passed_sec
                elif field != 'current_week':
                    sn = int(field[-1])
                    total_sec = shift_dict[sn]['total_second']
                    pass_sec = shift_dict[sn]['passed_second']

                if vt == 'Target':
                    target_value = None
                    if today_target_dict and idx in today_target_dict:
                        target_value = today_target_dict[idx]
                        tmp_dict[field] = target_value
                    elif field == 'current_week' and idx in current_week_target_dict:
                        target_value = current_week_target_dict[idx]
                        tmp_dict[field] = target_value

                    if target_value is not None:
                        if total_sec > 0 and pass_sec > 0:
                            if field == 'today':
                                f_p_d_dict[plan_idx] = 0
                                s1_index = mt + '_Planned_shift_1'
                                s2_index = mt + '_Planned_shift_2'
                                s3_index = mt + '_Planned_shift_3'
                                if s1_index in f_p_d_dict:
                                    f_p_d_dict[plan_idx] += f_p_d_dict[s1_index]
                                if s2_index in f_p_d_dict:
                                    f_p_d_dict[plan_idx] += f_p_d_dict[s2_index]
                                if s3_index in f_p_d_dict:
                                    f_p_d_dict[plan_idx] += f_p_d_dict[s3_index]
                            elif field == 'current_week':
                                f_p_d_dict[plan_idx] = 0
                                if mt in before_day_target_dict:
                                    f_p_d_dict[plan_idx] = before_day_target_dict[mt]
                                today_planned_idx = mt + '_Planned_today'
                                if today_planned_idx in f_p_d_dict:
                                    f_p_d_dict[plan_idx] += f_p_d_dict[today_planned_idx]
                            else:
                                sn = int(field[-1])
                                if int_shift_num > sn:
                                    f_p_d_dict[plan_idx] = target_value
                                else:
                                    f_p_d_dict[plan_idx] = calc_planned_num(target_value, pass_sec, total_sec)

                elif vt == 'Planned' and plan_idx in f_p_d_dict:
                    tmp_dict[field] = f_p_d_dict[plan_idx]
                elif vt == 'Forecast' and mt in current_pv_data:
                    cur_dict = current_pv_data[mt]
                    if field in cur_dict and total_sec > 0 and pass_sec > 0:
                        cur_val = cur_dict[field]
                        if field == 'today':
                            tmp_dict[field] = calc_forecast_num(cur_val, pass_sec, total_sec)
                        elif field == 'current_week':
                            tmp_dict[field] = 0
                        else:
                            sn = int(field[-1])
                            if int_shift_num > sn:
                                tmp_dict[field] = cur_val
                            else:
                                tmp_dict[field] = calc_forecast_num(cur_val, pass_sec, total_sec)

            if vt == 'Current' and mt in current_pv_data:
                tmp_dict = dict(tmp_dict, **current_pv_data[mt])
            tmp_list.append(tmp_dict)
        try:
            target_row = tmp_list[0]
            current_row = tmp_list[1]
            forecast_row = tmp_list[2]
            planned_row = tmp_list[3]
            deviation_row = tmp_list[4]
            forecast_row['current_week'] = target_row['current_week'] + current_row['current_week'] - planned_row[
                'current_week']
            if type(current_row['shift_1']) is int and type(planned_row['shift_1']) is int:
                deviation_row['shift_1'] = current_row['shift_1'] - planned_row['shift_1']
            if type(current_row['shift_2']) is int and type(planned_row['shift_2']) is int:
                deviation_row['shift_2'] = current_row['shift_2'] - planned_row['shift_2']
            if type(current_row['shift_3']) is int and type(planned_row['shift_3']) is int:
                deviation_row['shift_3'] = current_row['shift_3'] - planned_row['shift_3']
            if type(current_row['today']) is int and type(planned_row['today']) is int:
                deviation_row['today'] = current_row['today'] - planned_row['today']
            if type(current_row['current_week']) is int and type(planned_row['current_week']) is int:
                deviation_row['current_week'] = current_row['current_week'] - planned_row['current_week']
        except IndexError:
            print('IndexError')
        val_con[mt] = tmp_list
    return val_con


# 从集合中获取最小值作为产量
def get_coreshop_current_pv(pv_list):
    current_val = None
    multiply_by_2_tuple = ('0465319', '0465277', '0465359', '0465296', '0465299', '0465301', '0465302', '0465297',
                           '0465298', '0465242', '0465247', '0465244', '0465245', '0465246', '0465351', '0465353',
                           '0465358', '0465354', '0465355')
    for (k, pv) in pv_list.items():
        if k in multiply_by_2_tuple:
            cv = pv['ok'] * 2 - pv['nok']
        else:
            cv = pv['ok'] - pv['nok']
        if current_val is None or current_val > cv:
            current_val = cv
    return current_val


# 获取coreshop一天各班次的产值
def get_coreshop_day_shift_pv_info(db_engine, num, shift_dict, shift_num):
    pv_dict = {}
    for n, shift in shift_dict.items():
        time_from = None
        time_to = None
        if shift_num == n:
            time_from = shift['time_from']
            time_to = time.strftime('%d.%m.%Y %T')
        elif shift_num > n:
            time_from = shift['time_from']
            time_to = shift['time_to']
        pv_item_dict = {}
        if time_from is not None:
            pv_item_dict['ok'] = query_ok_pv_by_shift(db_engine, num, time_from, time_to)
            pv_item_dict['nok'] = query_nok_pv_by_shift(db_engine, num, time_from, time_to)
            pv_dict[n] = pv_item_dict
    return pv_dict


# 获取一段时间的产量
def get_coreshop_time_range_pv_info(db_engine, num, time_from, time_to):
    pv_item_dict = {}
    if time_from is not None:
        pv_item_dict['ok'] = query_ok_pv_by_shift(db_engine, num, time_from, time_to)
        pv_item_dict['nok'] = query_nok_pv_by_shift(db_engine, num, time_from, time_to)
    return pv_item_dict


def query_ok_pv_by_shift(db_engine, num, time_from, time_to):
    sql = "SELECT COUNT(*) cnt FROM kernschussdaten WHERE rtns LIKE '%" + num + \
          "%' AND zeitpunkt >= to_date('" + time_from + \
          "', 'dd.mm.yyyy hh24:mi:ss') AND zeitpunkt < to_date('" + time_to + \
          "', 'dd.mm.yyyy hh24:mi:ss')"
    result_proxy = db_engine.execute(sql)
    row = result_proxy.fetchone()
    return row['cnt']


def query_nok_pv_by_shift(db_engine, num, time_from, time_to):
    sql = "SELECT COUNT(*) cnt FROM rohdaten WHERE rohteil_nr='" + num + \
          "' AND ereignis_nr=2 AND zeitpunkt >= to_date('" + time_from + \
          "', 'dd.mm.yyyy hh24:mi:ss') AND zeitpunkt < to_date('" + time_to + \
          "', 'dd.mm.yyyy hh24:mi:ss')"
    result_proxy = db_engine.execute(sql)
    row = result_proxy.fetchone()
    return row['cnt']


def rebuild_rtns(rtns):
    arr = rtns.split(';00;')
    new_arr = []
    for val in arr:
        if val is not '' and val not in new_arr:
            new_arr.append(val)
    new_arr = sorted(new_arr)
    jn = '-00,'
    return jn.join(new_arr) + '-00'
